﻿// Copyright (c) 2014 Jonathan Magnan (http://jonathanmagnan.com/).
// All rights reserved (http://zzzproject.com/sqlbulkcopy-extensions/).
// Licensed under MIT License (MIT) (https://zsqlbulkcopy.codeplex.com/license/).
namespace Z.Net.Extensions
{
    internal partial class SqlBulkOperation
    {
        /// <summary>The SQL drop table.</summary>
        private const string SqlDropTable = "DROP TABLE {0}";

        /// <summary>The SQL delete action bulk.</summary>
        private const string SqlDeleteActionBulk = "DELETE FROM A FROM {0} AS A INNER JOIN {1} AS B ON {2}";

        /// <summary>The SQL merge insert action bulk.</summary>
        private const string SqlMergeInsertActionBulk = "INSERT INTO {0} ({3}) SELECT {4} FROM {1} AS B WHERE NOT EXISTS (SELECT 1 FROM {0} AS A WHERE {2})";

        /// <summary>The SQL update action bulk.</summary>
        private const string SqlUpdateActionBulk = "UPDATE A SET {2} FROM {0} AS A INNER JOIN {1} AS B ON {3}";

        /// <summary>The SQL delete action single.</summary>
        private const string SqlDeleteActionSingle = "DELETE FROM {0} WHERE {2}";

        /// <summary>The SQL insert action single.</summary>
        private const string SqlInsertActionSingle = "INSERT INTO {0} ({2}) VALUES ({3})";

        /// <summary>The SQL update action single.</summary>
        private const string SqlUpdateActionSingle = "UPDATE {0} SET {2} WHERE {3}";

        /// <summary>The SQL merge action single.</summary>
        private const string SqlMergeActionSingle = "UPDATE {0} SET {2} WHERE {3} INSERT INTO {0} ({4}) SELECT {5} WHERE NOT EXISTS (SELECT 1 FROM {0} WHERE {3})";

        /// <summary>Type of the SQL get information SQL.</summary>
        private const string SqlGetInformationSqlType = "SELECT * FROM {0} WHERE 1 = 0";

        /// <summary>Get database information for bulk operation.</summary>
        private const string SqlGetInformation = @"
SELECT  c.name AS Value ,
        1 AS SortOrder
FROM    sys.objects o
        INNER JOIN sys.columns c ON o.object_id = c.object_id
WHERE   c.is_identity = 1
        AND o.object_id = OBJECT_ID(@ObjectName)
UNION ALL
SELECT  CASE WHEN PATINDEX('%SQL Azure%', @@VERSION) > 0 THEN '1'
             ELSE '0'
        END ,
        2 AS SortOrder
UNION ALL
SELECT  COL_NAME(ic.object_id, ic.column_id) AS columnName ,
        3 AS SortOrder
FROM    sys.indexes AS i
        INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id
                                              AND i.index_id = ic.index_id
WHERE   i.is_primary_key = 1
        AND ic.object_id = OBJECT_ID(@ObjectName)
UNION ALL
SELECT  '[' + c.NAME + '] ' + CASE WHEN c.is_computed = 1 THEN 'AS ' + cc.[definition]
                                   ELSE UPPER(tp.name) + CASE WHEN tp.name IN ( 'varchar', 'char', 'varbinary', 'binary' )
                                                              THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX'
                                                                              ELSE CAST(c.max_length AS VARCHAR(5))
                                                                         END + ')'
                                                              WHEN tp.name IN ( 'nvarchar', 'nchar' ) THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX'
                                                                                                                      ELSE CAST(c.max_length / 2 AS VARCHAR(5))
                                                                                                                 END + ')'
                                                              WHEN tp.name IN ( 'datetime2', 'time2', 'datetimeoffset' )
                                                              THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
                                                              WHEN tp.name = 'decimal'
                                                              THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
                                                              ELSE ''
                                                         END + CASE WHEN c.collation_name IS NOT NULL THEN ' COLLATE ' + c.collation_name
                                                                    ELSE ''
                                                               END + ' NULL'
                              END + CHAR(13) ,
        4 AS SortOrder
FROM    sys.columns c WITH ( NOWAIT )
        JOIN sys.types tp WITH ( NOWAIT ) ON c.user_type_id = tp.user_type_id
        LEFT JOIN sys.computed_columns cc WITH ( NOWAIT ) ON c.[object_id] = cc.[object_id]
                                                             AND c.column_id = cc.column_id
        LEFT JOIN sys.default_constraints dc WITH ( NOWAIT ) ON c.default_object_id != 0
                                                                AND c.[object_id] = dc.parent_object_id
                                                                AND c.column_id = dc.parent_column_id
        LEFT JOIN sys.identity_columns ic WITH ( NOWAIT ) ON c.is_identity = 1
                                                             AND c.[object_id] = ic.[object_id]
                                                             AND c.column_id = ic.column_id
WHERE   c.[object_id] = OBJECT_ID(@ObjectName)
ORDER BY SortOrder
";

        /// <summary>The SQL Batch operation with where clause.</summary>
        private const string SqlBatchOperation = @"
DECLARE @BatchSize INT
DECLARE @NbItem INT
DECLARE @I INT

SET @BatchSize = {3}
SET @NbItem = (SELECT COUNT(1) FROM {0})
SET @I = 1

WHILE @I <= @NbItem
BEGIN
    {1}
    WHERE [{2}] >= @I AND [{2}] < @I + @BatchSize

    SET @I = @I + @BatchSize
END
";

        /// <summary>The SQL Batch operation with and clause.</summary>
        private const string SqlBatchOperationAnd = @"
DECLARE @BatchSize INT
DECLARE @NbItem INT
DECLARE @I INT

SET @BatchSize = {3}
SET @NbItem = (SELECT COUNT(1) FROM {0})
SET @I = 1

WHILE @I <= @NbItem
BEGIN
    {1}
    AND [{2}] >= @I AND [{2}] < @I + @BatchSize

    SET @I = @I + @BatchSize
END
";
    }
}